package cn.com.jrj.service;

import java.util.List;

import cn.com.jrj.base.db.CustomerType;
import cn.com.jrj.base.service.AbstractService;
import cn.com.jrj.domain.Quote;
import cn.com.jrj.domain.StockInfo;

/**
 * Service类,提供股票相关的操作方法(数据库相关)
 * 
 * @author xiaoyu.dou
 * 
 */
public class StockService extends AbstractService {
	public static final String SQL_CREATE_T_STOCK_QUOTE = "CREATE TABLE ##T_Stock_Quote(	[Stock_Id] [varchar](10) NOT NULL,	[Stock_Code] [varchar](6) NOT NULL,	[Stock_Name] [varchar](20) NULL,	[Price] [float] NULL,	[ZDF] [float] NULL,PRIMARY KEY CLUSTERED(	[Stock_Code] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]";	
	
	public static final String SQL_DROP_T_STOCK_QUOTE = "Drop table ##T_Stock_Quote";
	
	/** 预警短信下发状态-已下发 */
	public static final int SMS_STATUS_SENT = 1;

	/** 预警短信下发状态-未下发 */
	public static final int SMS_STATUS_UNSEND = 0;

	@Override
	public CustomerType getCustomerType() {
		return CustomerType.vSystem_TXTZGW;
	}

	/**
	 * 获取满足预警条件的自选股列表
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<StockInfo> getMyStockInfoList() {
		String sql = "select s.SR_ID,s.SR_InputNo , s.SR_Tel, s.SR_ID, q.price as now_price, s.SR_Conditions, s.SR_TPrice, q.stock_code, q.stock_name, q.zdf"
				+ " from vSystem_StocksRemind s"
				+ " right outer join ##T_Stock_Quote q on s.SR_Code = q.stock_code"
				+ " where s.SR_IsStatus=0 "
				+ " and ((s.SR_Conditions=2 and q.price>=s.SR_TPrice) or (s.SR_Conditions=1 and q.price<=s.SR_TPrice))";
		return this.jdbcTemplate.query(sql, new StockInfo());
	}

	/**
	 * 获取设置了手机短信预警的股票代码（用户绑定了正确的手机号并且状态为未下发的）
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String[] getSmsWarnStockcode() {
		String sql = "select distinct SR_Code from vSystem_StocksRemind s  where s.SR_IsStatus=0 and s.SR_Tel is not null";
		List<String> l = this.jdbcTemplate.queryForList(sql, String.class);
		String[] a = new String[l.size()];
		l.toArray(a);
		return a;
	}

	/**
	 * 写入股票行情到数据库表
	 * 
	 * @param list
	 * @return
	 */
	public int doAddQuotes(List<Quote> list) {
		if (list == null || list.size() == 0) {
			return 0;
		}
		
		// 先清空股票行情表
		String clearSql = "truncate table ##T_Stock_Quote";
		this.jdbcTemplate.execute(clearSql);

		String[] sql = new String[list.size()];

		StringBuffer sb = null;
		for (int i = 0; i < sql.length; i++) {
			Quote quote = (Quote) list.get(i);
			sb = new StringBuffer();
			sb.append("insert into ##T_Stock_Quote values ( ");
			sb.append("'" + quote.getStock_id() + "', ");
			sb.append("'" + quote.getStock_code() + "', ");
			sb.append("'" + quote.getStock_name() + "', ");
			sb.append(quote.getPrice() + ", ");
			sb.append(quote.getZdf());
			sb.append(")");
			sql[i] = sb.toString();
		}
		// TODO: 如何提高批量插入的效率
		int[] result = this.jdbcTemplate.batchUpdate(sql);

		return result.length;
	}

	/**
	 * 重置所有设置了短信预警的自选股的短信下发状态
	 * 
	 * @return 更新的记录数量
	 */
	public int doResetSmsStatus() {
		String sql = "update vSystem_StocksRemind set SR_IsStatus=0 where SR_IsStatus=1 ";
		return this.jdbcTemplate.update(sql);
	}

	/**
	 * 批量将已经下发短信了的自选股状态更新为"已下发"
	 * @param mystock_ids
	 * @return
	 */
	public int doBatchUpdateSmsStatus(String mystock_ids) {
		String sql = "update vSystem_StocksRemind set SR_IsStatus=1 where SR_ID in("+ mystock_ids +")";
		return this.jdbcTemplate.update(sql);
	}

	/**
	 * 重新创建临时行情表
	 * @return
	 */
	public int doRecreatQuoteTable() {
		int i = 0;
		try {
			this.jdbcTemplate.execute(SQL_DROP_T_STOCK_QUOTE);
		} catch (Exception e) {
		}
		try {
			this.jdbcTemplate.execute(SQL_CREATE_T_STOCK_QUOTE);
			i = 1;
		} catch (Exception e) {
			i = 0;
			e.printStackTrace();
		}
		return i;
	}

}
